package com.st.lotte.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.st.lotte.model.Code;
import com.st.lotte.model.TLO_DELY_TRCE_RECV_IF;
import com.st.lotte.model.TLO_DELY_TRCE_TRMN_IF;
import com.st.lotte.util.DBUtil;
import com.st.lotte.util.Database;
import com.st.lotte.util.LogTrace;
import com.sun.org.apache.bcel.internal.generic.DDIV;

/*
 * 롯데 정산관련 쿼리문 실행 DAO
 * 0. 공통 : 롯데 수신일자 기준 (RECV_DATE)
 * 1. 송장전체건수
 * 2. 정상완료된 건수
 * 3. 배송추적 미생성 송장 건수
 * 4. 유효성 오류 건수
 * 5. 지연수신 24시간이상 ~ 48시간이하
 * 6. 지연수신 48시간초과 ~ 72시간이하 
 * 7. 72시간초과
 * 
 * */
public class CalcurateDAO {
	private static final Logger log = LoggerFactory.getLogger(CalcurateDAO.class);
	
	Connection conn = null;
	Database db = new Database();
	
	// 커넥션 얻어오기
	public CalcurateDAO(){
		conn = db.getConnection();
	}
	
	// 커넥션 끊기
	protected synchronized void finalize() throws Throwable {
		conn.close();
	}; 	
	
//	public static void main(String[] args) {
//		String start = "2014-11-01";
//		String end  = "2014-11-25";
		
//		CalcurateDAO cDao = new CalcurateDAO();
//		Vector<TLO_DELY_TRCE_RECV_IF> list = cDao.totalCount(start, end);
//		Vector<TLO_DELY_TRCE_RECV_IF> list = cDao.completeCount(start, end);
//		Vector<TLO_DELY_TRCE_TRMN_IF> list = cDao.nonCompleteCount(start,end);
//		Vector<TLO_DELY_TRCE_TRMN_IF> list = cDao.invalidateCount(start,end);
//		Vector<TLO_DELY_TRCE_RECV_IF> list = cDao.delayOneTwo(start,end);
//		Vector<TLO_DELY_TRCE_RECV_IF> list = cDao.delayTwoThree(start,end);
//		Vector<TLO_DELY_TRCE_RECV_IF> list = cDao.delayThreeOver(start,end);
		
//		for (int i = 0; i < list.size(); i++) {
//			System.out.println(list.get(i).getRECV_DATE()+" "+list.get(i).getCNT());
//		}
//	}
	
	// 1. 송장전체 건수
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> totalCount(String start, String end){
		LogTrace.TraceLog("[totalCount] Complete!! - start");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.TOTAL_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
				dto.setRECV_DATE(rs.getDate("RECV_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[totalCount] Complete!! - end");
		
		return list;
	}
	
	// 2. 정상완료 건수
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> completeCount(String start, String end){
		LogTrace.TraceLog("[completeCount] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.COMPLETE_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
				dto.setRECV_DATE(rs.getDate("RECV_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[completeCount] Complete!! - end ");
		
		return list;		
	}
	
	// 3. 배송추적 미생성 송장 건수 
	public synchronized Vector<TLO_DELY_TRCE_TRMN_IF> nonCompleteCount(String start, String end){
		LogTrace.TraceLog("[nonCompleteCount] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_TRMN_IF> list = new Vector<TLO_DELY_TRCE_TRMN_IF>();
		try {
			String sql = DBUtil.NONCOMPLETE_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_TRMN_IF dto = new TLO_DELY_TRCE_TRMN_IF();
				dto.setTRMN_DATE(rs.getDate("TRMN_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[nonCompleteCount] Complete!! - end ");
		
		return list;
	}
	
	// 4. 유효성 오류건수 
	public synchronized Vector<TLO_DELY_TRCE_TRMN_IF> invalidateCount(String start, String end){
		LogTrace.TraceLog("[invalidateCount] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_TRMN_IF> list = new Vector<TLO_DELY_TRCE_TRMN_IF>();
		try {
			String sql = DBUtil.INVALIDATE_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_TRMN_IF dto = new TLO_DELY_TRCE_TRMN_IF();
				dto.setTRMN_DATE(rs.getDate("TRMN_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[invalidateCount] Complete!! - end ");
		
		return list;
	}
	
	// 5. 지연수신 24시간이상 ~ 48시간 이하 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> delayOneTwo(String start, String end){
		LogTrace.TraceLog("[delayOneTwo] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.DELAY_ONETWO_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
				dto.setRECV_DATE(rs.getDate("RECV_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[delayOneTwo] Complete!! - end ");
		
		return list;
	}
	
	// 6. 지연수신 48시간초과 ~ 72시간이하
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> delayTwoThree(String start, String end){
		LogTrace.TraceLog("[delayTwoThree] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.DELAY_TWOTHREE_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
				dto.setRECV_DATE(rs.getDate("RECV_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[delayTwoThree] Complete!! - end ");
		
		return list;
	}
	
	// 7. 지연수신 72시간초과 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> delayThreeOver(String start, String end){
		LogTrace.TraceLog("[delayThreeOver] Complete !! - start ");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.DELAY_THREEOVER_COUNT;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
				dto.setRECV_DATE(rs.getDate("RECV_DATE"));
				dto.setCNT(rs.getInt("count"));
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[delayThreeOver] Complete!! - end ");
		
		return list;
	}
	
}









